CREATE OR REPLACE FUNCTION evaluate_ticket_features() RETURNS VOID AS '
DECLARE
  v_ticketCount INTEGER;
  v_ticketsProcCount INTEGER;

  v_clearedCon INTEGER;
  v_clearedGat INTEGER;
  v_clearedConGat INTEGER;
  v_clearedRecall REAL;
  v_clearedPrecision REAL;
  v_clearedFMeasure REAL;

  v_cableCon INTEGER;
  v_cableGat INTEGER;
  v_cableConGat INTEGER;
  v_cableRecall REAL;
  v_cablePrecision REAL;
  v_cableFMeasure REAL;
  
  v_cutCon INTEGER;
  v_cutGat INTEGER;
  v_cutConGat INTEGER;
  v_cutRecall REAL;
  v_cutPrecision REAL;
  v_cutFMeasure REAL;  
  
  v_strucnoCon INTEGER;
  v_strucnoGat INTEGER;
  v_strucnoConGat INTEGER;
  v_strucnoStr INTEGER;
  v_strucnoStrGat INTEGER;
  
  v_strucnoRecall REAL;
  v_strucnoPrecision REAL;
  v_strucnoFMeasure REAL;

  v_freetextlinesConGat INTEGER;
  v_autotextlinesConGat INTEGER;
  v_freetextlinesDiff INTEGER;
  v_autotextlinesDiff INTEGER;
  v_freetextlinesAvgDiff REAL;
  v_autotextlinesAvgDiff REAL;
  
  v_dateOfLastTicketProcessed TIMESTAMP;
  v_dateOfFirstTicketProcessed TIMESTAMP;
BEGIN

  -- Total Number of Tickets
  SELECT COUNT(DISTINCT con."Ticket") INTO v_ticketCount 
      FROM consolidatedtable con
          , ticketfeatures tf
      WHERE con."Ticket" = tf.ticketnumber;

  -- Get the dates of first and last tickets processed
  IF EXISTS(SELECT a.attname 
    FROM pg_catalog.pg_stat_user_tables t, pg_catalog.pg_attribute a
    WHERE t.relid = a.attrelid AND LOWER(t.relname) = ''ticketfeatures''
      AND LOWER(a.attname) = ''dateprocessed'') THEN     
      SELECT MAX(tf.DateProcessed), MIN(tf.DateProcessed) 
          INTO v_dateOfLastTicketProcessed , v_dateOfFirstTicketProcessed
          FROM ticketfeatures tf;
  END IF;
      
  -- Number of Tickets Processed
  SELECT COUNT(*) INTO v_ticketsProcCount
      FROM ticketfeatures tf;

  -- Shunt_Or_Cleared
  SELECT COUNT(*) INTO v_clearedCon FROM consolidatedtable
      WHERE "SHUNT_OR_CLEARED" IS NOT NULL AND "SHUNT_OR_CLEARED" != '''';
  
  SELECT COUNT(*) INTO v_clearedGat FROM ticketfeatures
      WHERE Shunt_Or_Cleared IS NOT NULL AND Shunt_Or_Cleared != '''';
  
  SELECT COUNT(*) INTO v_clearedConGat
      FROM consolidatedtable con
          , ticketfeatures tf
      WHERE con."SHUNT_OR_CLEARED" IS NOT NULL AND con."SHUNT_OR_CLEARED" != ''''
          AND tf.Shunt_Or_Cleared IS NOT NULL AND tf.Shunt_Or_Cleared != ''''
          AND con."Ticket" = tf.ticketnumber;
  -- Cut_And_Rack
  SELECT COUNT(*) INTO v_cutCon FROM consolidatedtable
      WHERE "CUT_AND_RACK" IS NOT NULL AND "CUT_AND_RACK" != '''';
  
  SELECT COUNT(*) INTO v_cutGat FROM ticketfeatures
      WHERE Cut_And_Rack IS NOT NULL AND Cut_And_Rack != '''';
  
  SELECT COUNT(*) INTO v_cutConGat
      FROM consolidatedtable con
          , ticketfeatures tf
      WHERE con."CUT_AND_RACK" IS NOT NULL AND con."CUT_AND_RACK" != ''''
          AND tf.Cut_And_Rack IS NOT NULL AND tf.Cut_And_Rack != ''''
          AND con."Ticket" = tf.ticketnumber;

  -- StructureNumbers

  -- Get the number of structures
  SELECT COUNT(*) INTO v_strucnoStr
      FROM "ColumbiaConsolidatedTable_Structures_Phase3" con;

  -- Get the number of structures found by GATE
  SELECT COUNT(DISTINCT ts.structuretype||ts.structurenumber) INTO v_strucnoGat
      FROM ticketstructures ts;
      
  -- Get the number of structures found by GATE that are valid
  SELECT COUNT(DISTINCT ts.structuretype||ts.structurenumber) INTO v_strucnoStrGat 
      FROM ticketstructures ts, "ColumbiaConsolidatedTable_Structures_Phase3" con
      WHERE con."StructureType" = ts.structuretype
          AND con."StructureNumber" = ts.structurenumber;

  -- Get the number of structures found in tickets by the Perl scripts
  SELECT COUNT(*) INTO v_strucnoCon
      FROM compare_four_tkt_lists_1996_2006
      WHERE my_list_sb_mh = 1;

  -- Get the number of structures found by GATE and by Perl scripts.
  SELECT COUNT(*) INTO v_strucnoConGat 
      FROM ticketstructures ts, compare_four_tkt_lists_1996_2006 compare
      WHERE compare.ticket = ts.ticketnumber 
          AND compare.str_type = ts.structuretype
          AND compare.str_no = ts.structurenumber
          AND compare.my_list_sb_mh = 1;

  -- FreeTextLines and AutoTextLines
  SELECT SUM(con."FreeTextLinesInRemarks" - tf.freetextlinesinremarks)
      , SUM(con."AutoTextLines" - tf.autotextlines)
      INTO v_freetextlinesDiff, v_autotextlinesDiff
      FROM consolidatedtable con
      , ticketfeatures tf
      WHERE con."Ticket" = tf.ticketnumber;
  
  -- FreeTextLines 
  SELECT COUNT(*) INTO v_freetextlinesConGat 
      FROM consolidatedtable con
      , ticketfeatures tf
      WHERE con."Ticket" = tf.ticketnumber 
          AND con."FreeTextLinesInRemarks" = tf.freetextlinesinremarks;

  -- AutoTextLines
  SELECT COUNT(*) INTO v_autotextlinesConGat 
      FROM consolidatedtable con
      , ticketfeatures tf
      WHERE con."Ticket" = tf.ticketnumber 
          AND con."AutoTextLines" = tf.autotextlines;

  -- Cables
  SELECT COUNT(*) INTO v_cableCon FROM consolidatedtable
      WHERE "Cable_Fixes" = 1;
  
  SELECT COUNT(*) INTO v_cableGat FROM ticketfeatures
      WHERE cable_fixes = 1;
  
  SELECT COUNT(*) INTO v_cableConGat
      FROM consolidatedtable con
          , ticketfeatures tf
      WHERE con."Cable_Fixes" = 1
          AND tf.cable_fixes = 1
          AND con."Ticket" = tf.ticketnumber;
  
--Avoid dividing by zero    
  --shunt and cleared
  IF v_clearedCon = 0  THEN 
      v_clearedCon = 1;
  END IF;
  
  IF v_clearedGat = 0  THEN 
      v_clearedGat = 1;
  END IF;
  IF v_clearedConGat = 0 THEN
     v_clearedConGat = -1;
  END IF;
     
  --cut and rack
  IF v_cutCon = 0  THEN 
      v_cutCon = 1;
  END IF;
  
  IF v_cutGat = 0  THEN 
      v_cutGat = 1;
  END IF;
  
  IF v_cutConGat = 0 THEN
     v_cutConGat = -1;
  END IF;

  --cables
  IF v_cableCon = 0  THEN 
      v_cableCon = 1;  END IF;

  IF v_cableGat = 0  THEN 
      v_cableGat = 1;  END IF;

  IF v_cableConGat = 0 THEN
     v_cableConGat = -1;
  END IF;

  -- Shunt_Or_Cleared 
  v_clearedRecall := CAST(v_clearedConGat as REAL) / CAST(v_clearedCon as REAL);
  v_clearedPrecision := CAST(v_clearedConGat as REAL) / CAST(v_clearedGat as REAL);
  v_clearedFMeasure := (2 * v_clearedPrecision * v_clearedRecall) / (v_clearedPrecision + v_clearedRecall);
  
  -- Cut_And_Rack
  v_cutRecall := CAST(v_cutConGat as REAL) / CAST(v_cutCon as REAL);
  v_cutPrecision := CAST(v_cutConGat as REAL) / CAST(v_cutGat as REAL);
  v_cutFMeasure := (2 * v_cutPrecision * v_cutRecall) / (v_cutPrecision + v_cutRecall);

  -- Structure Number
  v_strucnoRecall := CAST(v_strucnoConGat as REAL) / CAST(v_strucnoCon as REAL);
  v_strucnoPrecision := CAST(v_strucnoStrGat as REAL) / CAST(v_strucnoGat as REAL);
  v_strucnoFMeasure := (2 * v_strucnoPrecision * v_strucnoRecall) / (v_strucnoPrecision + v_strucnoRecall);

  -- FreeTextLines
  v_freetextlinesAvgDiff := CAST(v_freetextlinesDiff as REAL) / CAST(v_ticketsProcCount as REAL);
  -- AutoTextLines
  v_autotextlinesAvgDiff := CAST(v_autotextlinesDiff as REAL) / CAST(v_ticketsProcCount as REAL);

  --Cable Sizes
  v_cableRecall := CAST(v_cableConGat as REAL) / CAST(v_cableCon as REAL);
  v_cablePrecision := CAST(v_cableConGat as REAL) / CAST(v_cableGat as REAL);
  v_cableFMeasure := (2 * v_cablePrecision * v_cableRecall) / (v_cablePrecision + v_cableRecall);

  RAISE NOTICE ''Total number of tickets: %'', v_ticketCount;
  RAISE NOTICE ''Number of tickets processed: %'', v_ticketsProcCount;
  RAISE NOTICE ''Percentage of tickets processed: %'', CAST(v_ticketsProcCount as REAL) / CAST(v_ticketCount as REAL) * 100.0;
  RAISE NOTICE ''Time taken: %'', CAST((v_dateOfLastTicketProcessed - v_dateOfFirstTicketProcessed) as INTERVAL);
  RAISE NOTICE ''======================================================='';
  RAISE NOTICE ''Shunt Or Cleared: recall: % precision % f-measure: %'', v_clearedRecall, v_clearedPrecision, v_clearedFMeasure;
  RAISE NOTICE ''Cut And Rack: recall: % precision % f-measure: %'', v_cutRecall, v_cutPrecision, v_cutFMeasure;
  RAISE NOTICE ''Structure Numbers: recall: % precision % f-measure: %'', v_strucnoRecall, v_strucnoPrecision, v_strucnoFMeasure;
  RAISE NOTICE ''Cable Fixes : recall: % precision % f-measure: %'', v_cableRecall, v_cablePrecision, v_cableFMeasure;
  RAISE NOTICE ''FreeTextLines: average difference (perl scripts - gate): % lines per ticket.'', v_freetextlinesAvgDiff;
  RAISE NOTICE ''AutoTextLines: average difference (perl scripts - gate): % lines per ticket.'', v_autotextlinesAvgDiff;

END;
' LANGUAGE 'plpgsql';

-- SELECT evaluate_ticket_features();
